<?php

/**
 * RP System
 *
 * @version 1.0
 * @copyright 2004 Ron Planken
 */

class signup {


	function get($raid_id = 0, $status = -1, $substitute = -1, $user_id = 0, $player_id = 0){

		global $dbh;

		if ($raid_id == 0) {
			$query ='SELECT  t.raid_id
							,t.player_id
							,t.turnin_id
							,t.substitute
							,t.status
							,t.signup_date
							,t.unsign_date
							,t.comment
						FROM turnin t
						JOIN player p ON p.player_id = t.player_id
							';
		} else {
			$query = "SELECT  t.turnin_id
							 ,t.player_id
							 ,p.user_id
							 ,t.raid_id
							 ,t.substitute
							 ,t.status
							 ,t.signup_date
							 ,t.unsign_date
							 ,t.comment
							 ,p.class_id
							 ,p.name
							 ,c.name as 'class'
							 ,c.small_picture as 'class_picture'
						     ,p.team_id
						     ,te.team_image
						     ,p.class_role_id
							 ,cr.name as 'class_role_name'
							 ,r.name as 'role_name'
							 ,r.id as 'role_id'
							 ,r.small_picture as 'role_picture'
							 ,c.color_code
						FROM  turnin t
						JOIN  player p ON t.player_id = p.player_id
					    JOIN team te ON te.team_id = p.team_id
					    JOIN class c ON c.class_id = p.class_id
                   LEFT JOIN class_role cr ON p.class_role_id = cr.id
				   LEFT JOIN role r ON cr.role_id = r.id
					   WHERE  raid_id = $raid_id
					 							";
			if ($status > -1) {
				$query .= " AND status = $status";
			}
			if ($substitute > -1) {
				$query .= " AND substitute = $substitute";
			}
			if ($user_id > 0) {
				$query .= " AND p.user_id = $user_id";
			}
			if ($player_id > 0) {
				$query .= " AND p.player_id = $player_id";
			}
			$query .= " ORDER BY p.team_id DESC, p.class_id ASC;";

		}

		$signups = SelectMultiQuery($query, $dbh);


		return $signups;

	}


	function getWeekHistory($user_id = 0, $status, $event_id = 0)
	{
		global $dbh;

		if ($user_id > 0) {

			$query = "SELECT YEAR(r.date_time) as 'year'
						,MONTH(r.date_time) as 'month'
						,WEEK(r.date_time) as 'week'
						,COUNT(WEEK(r.date_time)) as 'count'
					FROM raid r 
					JOIN turnin t ON t.raid_id = r.raid_id
					JOIN player p ON p.player_id = t.player_id
				   WHERE p.user_id = $user_id
				     AND t.status = $status";

			if ($event_id > 0) {
				$query .= " AND r.event_id = $event_id";
			}
		}
		else
		{
			$query = "SELECT YEAR(r.date_time) as 'year'
						,MONTH(r.date_time) as 'month'
						,WEEK(r.date_time) as 'week'
						,COUNT(WEEK(r.date_time)) as 'count'
					FROM raid r";
			if ($event_id > 0) {
				$query .= " WHERE r.event_id = $event_id";
			}

		}
		$query .= " GROUP BY YEAR(r.date_time)";

		$history = SelectMultiQuery($query, $dbh);


		return $history;
	}

	function getHistory($type = 'global', $value = 0, $status = 0, $from = '', $to = '')
	{
		global $dbh;

		$from = secure_string($from);
		$to = secure_string($to);
		
		if($type == 'global' && $value == 0):
		$query = "SELECT	r.event_id
					       ,COUNT(raid_id) as 'count'
					       ,e.name
					       ,e.event_id
					       ,e.description
					  FROM  raid r
					  JOIN  event e ON e.event_id = r.event_id
					  WHERE e.status = 0
				   GROUP BY r.event_id";

		$history = SelectMultiQuery($query, $dbh);

		elseif($type == 'global' && $value > 0):
		$query = "SELECT	r.event_id
					       ,COUNT(r.raid_id) as 'count'
					       ,e.name
					       ,e.event_id
					       ,e.description
 					   FROM raid r
 					   JOIN event e ON e.event_id = r.event_id
					   JOIN turnin t ON t.raid_id = r.raid_id
					   JOIN player p ON p.player_id = t.player_id
				      WHERE p.user_id = $value
				        AND t.status = $status
					    AND e.status = 0
				   GROUP BY r.event_id";

		$history = SelectMultiTextQuery($query, $dbh);

		elseif($type == 'defined' && $value > 0 && $from != '' && $to != ''):
		$query = "SELECT	r.event_id
					       ,COUNT(r.raid_id) as 'count'
					       ,e.name
					       ,e.event_id
					       ,e.description
 					   FROM raid r
 					   JOIN event e ON e.event_id = r.event_id
					   JOIN turnin t ON t.raid_id = r.raid_id
					   JOIN player p ON p.player_id = t.player_id
				      WHERE p.user_id = $value
				        AND t.status = $status
					    AND e.status = 0
					  	AND r.date_time < $from
						AND r.date_time > $to
				   GROUP BY r.event_id";

		$history = SelectMultiTextQuery($query, $dbh);

		elseif($type == 'defined' && $value == 0):
		$query = "SELECT	r.event_id
					       ,COUNT(raid_id) as 'count'
					       ,e.name
					       ,e.event_id
					       ,e.description
					  FROM  raid r
					  JOIN  event e ON e.event_id = r.event_id
					  WHERE e.status = 0
					  	AND r.date_time < $from
						AND r.date_time > $to
				   GROUP BY r.event_id";

		$history = SelectMultiQuery($query, $dbh);
		
		endif;

		return $history;

	}

	function getMonthHistory($user_id = 0, $status, $event_id = 0)
	{
		global $dbh;

		if ($user_id > 0) {

			$query = "SELECT YEAR(r.date_time) as 'year'
						,MONTH(r.date_time) as 'month'
						,COUNT(MONTH(r.date_time)) as 'count'
					FROM raid r 
					JOIN turnin t ON t.raid_id = r.raid_id
					JOIN player p ON p.player_id = t.player_id
				   WHERE p.user_id = $user_id
				     AND t.status = $status";


			if ($event_id > 0) {
				$query .= " AND r.event_id = $event_id";
			}
		}
		else
		{
			$query = "SELECT YEAR(r.date_time) as 'year'
						,MONTH(r.date_time) as 'month'
						,COUNT(MONTH(r.date_time)) as 'count'
					FROM raid r ";
			if ($event_id > 0) {
				$query .= " WHERE r.event_id = $event_id";
			}

		}
		$query .= " GROUP BY YEAR(r.date_time)";

		$history = SelectMultiQuery($query, $dbh);


		return $history;
	}


	function getYearHistory($user_id = 0, $status, $event_id = 0)
	{
		global $dbh;

		if ($user_id > 0) {

			$query = "SELECT YEAR(r.date_time) as 'year'
						,COUNT(YEAR(r.date_time)) as 'count'
					FROM raid r 
					JOIN turnin t ON t.raid_id = r.raid_id
					JOIN player p ON p.player_id = t.player_id
				   WHERE p.user_id = $user_id
				     AND t.status = $status";


			if ($event_id > 0) {
				$query .= " AND r.event_id = $event_id";
			}
		}
		else
		{
			$query = "SELECT YEAR(r.date_time) as 'year'
						,COUNT(YEAR(r.date_time)) as 'count'
					FROM raid r";
			if ($event_id > 0) {
				$query .= " WHERE r.event_id = $event_id";
			}

		}
		$query .= " GROUP BY YEAR(r.date_time)";

		$history = SelectMultiQuery($query, $dbh);


		return $history;
	}


	function getDetailedHistory($user_id, $status, $event_id)
	{
		global $dbh;

		/*
		SELECT r.raid_id
		,p.name
		,r.date_time
		,WEEK(r.date_time) as 'week'
		,MONTH(r.date_time) as 'month'
		,YEAR(r.date_time) as 'year'
		,r.freeze_time
		,r.no_players
		,r.substitute_max
		,r.class_max
		,r.description
		,e.name AS 'event_name'
		,r.leader_player_id
		,r.mt1_player_id
		,r.mt2_player_id
		,r.event_id
		,r.status
		,r.main_team_id
		,t1.team_order AS 'main_team_order'
		,r.sub_team_id
		,t2.team_order AS 'sub_team_order'
		,r.rp_category_id
		,IFNULL(sum(rp_gained ) - sum( rp_spend ), 0) AS rp
		FROM raid r
		JOIN turnin t ON t.raid_id = r.raid_id
		JOIN player p ON p.player_id = t.player_id
		JOIN event e ON e.event_id = r.event_id
		JOIN team t1 ON t1.team_id = r.main_team_id
		JOIN team t2 ON t2.team_id = r.sub_team_id
		LEFT JOIN raid_points rp ON r.raid_id = rp.raid_id
		AND t.player_id = rp.player_id
		WHERE p.user_id = $user_id
		AND t.status = $status
		*/

		$query = "SELECT r.event_id
						,r.raid_id
						,p.name
						,r.date_time
						,WEEK(r.date_time) as 'week'
						,MONTH(r.date_time) as 'month'
						,YEAR(r.date_time) as 'year'
						,r.freeze_time
						,r.no_players
						,r.substitute_max
						,r.class_max
						,r.description
						,e.name AS 'event_name'
						,r.status
						,r.rp_category_id
						,IFNULL(sum(rp_gained ) - sum( rp_spend ), 0) AS rp
					FROM raid r
					JOIN turnin t ON t.raid_id = r.raid_id
					JOIN player p ON p.player_id = t.player_id
					JOIN event e ON e.event_id = r.event_id
			   		JOIN raid_points rp ON r.raid_id = rp.raid_id
		      		 AND t.player_id = rp.player_id
				   WHERE p.user_id = $user_id
					 AND t.status = $status
					 	";
		if ($event_id > 0) {
			$query .= " AND e.event_id = $event_id";
		}
		$query .= " GROUP BY raid_id
				    ORDER BY r.date_time";


		$history = SelectMultiQuery($query, $dbh);


		return $history;


	}

	function getActualHistory($type = 'global', $value = 0, $status = 0, $from = '', $to = '')
	{
		global $dbh;

		$from = secure_string($from);
		$to = secure_string($to);
		
		if($type == 'global' && $value > 0):
			$query = "SELECT r.event_id
							FROM raid r
							JOIN event e ON e.event_id = r.event_id
					   		JOIN raid_points rp ON r.raid_id = rp.raid_id
						   WHERE rp.user_id = $value
							 AND e.status = 0
					    GROUP BY rp.raid_id, r.event_id
					    ORDER by r.event_id";
			$history = SelectMultiQuery($query, $dbh);
	
			$history_count = count($history);
	
			for ($i = 0; $i != $history_count; $i++):
			
				$real_history[$history[$i]['event_id']]++;
	
			endfor;

		elseif($type == 'defined' && $value > 0 && $from != '' && $to != ''):
			$query = "SELECT r.event_id
							FROM raid r
							JOIN event e ON e.event_id = r.event_id
					   		JOIN raid_points rp ON r.raid_id = rp.raid_id
						   WHERE rp.user_id = $value
							 AND e.status = 0
					  		 AND r.date_time < $from
							 AND r.date_time > $to
					    GROUP BY rp.raid_id, r.event_id
					    ORDER by r.event_id";
			$history = SelectMultiQuery($query, $dbh);
	
			$history_count = count($history);
	
			for ($i = 0; $i != $history_count; $i++):
			
				$real_history[$history[$i]['event_id']]++;
	
			endfor;

		endif;

		return $real_history;

	}

	function count($raid_id = 0, $status = -1, $substitute = -1, $user_id = 0, $class = 0, $role_id = -1, $class_role_id = -1, $raid_role_id = -1){

		global $dbh;

		if ($raid_id == 0) {
			$query ='SELECT  count(raid_id) as sign_ups
						FROM turnin
					GROUP BY raid_id
							';


		} else {
			$query = "SELECT count(t.raid_id) as sign_ups
						FROM turnin t
							";
			if ($user_id > 0 || $class <> "") {
				$query .= " JOIN player p ON t.player_id = p.player_id";
			}

			if ($raid_role_id > 1 || $role_id > -1 || $class_role_id > -1) {
				$query .= " JOIN raid_role r ON r.id = t.raid_role_id";
			}

			$query .= " WHERE t.raid_id = $raid_id";

			if ($user_id > 0) {
				$query .= " AND p.user_id = $user_id";
			}
			if ($class <> "") {
				$query .= " AND p.class_id = $class";
			}
			if ($role_id > -1) {
				$query .= " AND r.role_id = $role_id";
			}
			if ($raid_role_id > -1) {
				$query .= " AND r.id = $raid_role_id";
			}
			if ($class_role_id > -1) {
				$query .= " AND r.class_role_id = $class_role_id";
			}
			if ($status > -1) {
				$query .= " AND t.status = $status";
			}
			if ($substitute > -1) {
				$query .= " AND t.substitute = $substitute";
			}


		}

		$signups = SelectMultiQuery($query, $dbh);

		return $signups;

	}


	function countClassRoles($raid_id = 0, $status = -1, $substitute = -1, $user_id = 0, $class = 0){

		global $dbh;

		$query = "SELECT count(t.raid_id) as sign_ups
						FROM turnin t
							";
		if ($user_id > 0 || $class <> "") {
			$query .= " JOIN player p ON t.player_id = p.player_id";
		}

		$query .= " WHERE t.raid_id = $raid_id";

		if ($user_id > 0) {
			$query .= " AND p.user_id = $user_id";
		}
		if ($class <> "") {
			$query .= " AND p.class_id = $class";
		}
		if ($status > -1) {
			$query .= " AND t.status = $status";
		}
		if ($substitute > -1) {
			$query .= " AND t.substitute = $substitute";
		}


		$signups = SelectMultiQuery($query, $dbh);

		return $signups;

	}

	function save($turnin_id = 0, $player_id = 0, $raid_id = 0, $substitute = 0, $status = 0, $signup_date = 0, $unsign_date = 0, $comment = "", $raid_role_id = 0) {


		global $dbh;

		$comment = secure_string($comment);
		$signup_date = secure_string($signup_date);
		$turnin_id = secure_int($turnin_id);
		$player_id = secure_int($player_id);
		$raid_id = secure_int($raid_id);
		$substitute = secure_int($substitute);
		$status = secure_int($status);
		$raid_role_id = secure_int($raid_role_id);



		if ($turnin_id == 0) {
			$query ="INSERT INTO turnin
						   (turnin_id
						   ,player_id
						   ,raid_id
						   ,substitute
						   ,status
						   ,signup_date
						   ,unsign_date
						   ,comment
						   ,raid_role_id)
					VALUES ($turnin_id
						   ,$player_id
						   ,$raid_id
						   ,$substitute
						   ,$status
						   ,now()
						   ,$unsign_date
						   ,$comment
						   ,$raid_role_id)
							";
			$status = InsertQuery($query, $dbh);
		} else {

			$query ="UPDATE turnin
						SET player_id = $player_id
						   ,raid_id = $raid_id
						   ,substitute = $substitute
						   ,status = $status
						   ,signup_date = now()
						   ,unsign_date = $unsign_date
						   ,comment = $comment
					       ,raid_role_id = $raid_role_id
					  WHERE turnin_id = $turnin_id
							";

			$status = UpdateQuery($query, $dbh);
		}

		return $status;

	}
}
?>
